﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyKho.Common;
using System.Data.SqlClient;

namespace QuanLyKho.DataAccess
{
    public class clsDataAccessListProvince : iAction
    {
        private SqlConnection conn = new clsConnect().GetSqlConnection();
        private SqlCommand cmd = null;
        private SqlDataReader dr = null;
        private StringBuilder sBuilder = null;

        #region iAction Members
            public int AddNew(object objParam)
            {
                try
                {
                    int status = 0;
                    conn.Open();
                    clsProvince obj = (clsProvince)objParam;
                    sBuilder = new StringBuilder("Sp_listProvince_Add");
                    cmd = new SqlCommand(sBuilder.ToString(), conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Code", obj.Code);
                    cmd.Parameters.AddWithValue("@Name", obj.Name);                    
                    cmd.Parameters.AddWithValue("@ShortName", obj.ShortName);
                    cmd.Parameters.AddWithValue("@Des", obj.Des);
                    cmd.Parameters.AddWithValue("@IDParent", obj.IDParent);
                    status = cmd.ExecuteNonQuery();
                    conn.Close();
                    return status;
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { conn.Close(); }
            }

            public int Update(object objParam)
            {
                try
                {
                    int status = 0;
                    conn.Open();
                    clsProvince obj = (clsProvince)objParam;
                    sBuilder = new StringBuilder("Sp_listProvince_Update");
                    cmd = new SqlCommand(sBuilder.ToString(), conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", obj.ID);
                    cmd.Parameters.AddWithValue("@Code", obj.Code);
                    cmd.Parameters.AddWithValue("@Name", obj.Name);                    
                    cmd.Parameters.AddWithValue("@ShortName", obj.ShortName);
                    cmd.Parameters.AddWithValue("@Des", obj.Des);
                    cmd.Parameters.AddWithValue("@IDParent", obj.IDParent);
                    status = cmd.ExecuteNonQuery();
                    conn.Close();
                    return status;
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { conn.Close(); }
            }

            public int Delete(long id)
            {
                try
                {
                    int status = 0;
                    conn.Open();
                    sBuilder = new StringBuilder("Sp_listProvince_Delete");
                    cmd = new SqlCommand(sBuilder.ToString(), conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", id);
                    status = cmd.ExecuteNonQuery();
                    conn.Close();
                    return status;
                }
                catch (Exception ex)
                {throw ex;}
                finally
                { conn.Close();}
            }

        #endregion

            public List<clsProvince> ListProvinceSelectAll()
            {
                try
                {
                    List<clsProvince> list = new List<clsProvince>();
                    sBuilder = new StringBuilder("Sp_listProvince_SelectAll");
                    conn.Open();
                    cmd = new SqlCommand(sBuilder.ToString(), conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            clsProvince obj = new clsProvince();
                            if (dr["ID"] != DBNull.Value)
                                obj.ID = Convert.ToInt64(dr["ID"]);
                            if (dr["Code"] != DBNull.Value)
                                obj.Code = Convert.ToString(dr["Code"]);
                            if (dr["Name"] != DBNull.Value)
                                obj.Name = Convert.ToString(dr["Name"]);
                            if (dr["ShortName"] != DBNull.Value)
                                obj.ShortName = Convert.ToString(dr["ShortName"]);
                            if (dr["Des"] != DBNull.Value)
                                obj.Des = Convert.ToString(dr["Des"]);
                            if (dr["IDParent"] != DBNull.Value)
                                obj.IDParent = Convert.ToInt64(dr["IDParent"]); 
                            list.Add(obj);
                        }
                    }
                    conn.Close();
                    return list;
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { conn.Close(); }
            }


            public List<clsProvince> ListProvinceSelectOneWithId(long id)
            {
                try
                {
                    List<clsProvince> list = new List<clsProvince>();
                    sBuilder = new StringBuilder("Sp_listProvince_SelectOne");
                    conn.Open();
                    cmd = new SqlCommand(sBuilder.ToString(), conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", id);
                    dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            clsProvince obj = new clsProvince();
                            if (dr["ID"] != DBNull.Value)
                                obj.ID = Convert.ToInt64(dr["ID"]);
                            if (dr["Code"] != DBNull.Value)
                                obj.Code = Convert.ToString(dr["Code"]);
                            if (dr["Name"] != DBNull.Value)
                                obj.Name = Convert.ToString(dr["Name"]);
                            if (dr["ShortName"] != DBNull.Value)
                                obj.ShortName = Convert.ToString(dr["ShortName"]);
                            if (dr["Des"] != DBNull.Value)
                                obj.Des = Convert.ToString(dr["Des"]);
                            if (dr["IDParent"] != DBNull.Value)
                                obj.IDParent = Convert.ToInt64(dr["IDParent"]); 
                            list.Add(obj);
                        }
                    }
                    conn.Close();
                    return list;
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { conn.Close(); }
            }


    }
}
